package com.scaffold.springmvc.dao;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Repository;

import wangjg.commons.dao.ResultMapping;
import wangjg.commons.dao.ResultMapping.ColumnInfo;

import com.scaffold.springmvc.entity.UserInfo;

/**
 * 用户DAO
 * @author wangjg
 */
@Repository
public class SqlDAO extends BasicBizDAO<UserInfo, Long> {
	
	@Autowired
	private DataSource dataSource;
	
	private Connection getConnection() throws SQLException{
		return DataSourceUtils.getConnection(dataSource);
	}
	
	public int update(String sql) throws SQLException {
		Connection conn = getConnection();
		Statement stmt = conn.createStatement();
		int n = stmt.executeUpdate(sql);
		conn.commit();
		stmt.close();
		return n;
	}

	public ResultVo query(String sql) throws SQLException {
		Statement stmt = getConnection().createStatement();
		ResultSet rs = stmt.executeQuery(sql);

		ResultVo result = this.getResultVo(rs);
		
		stmt.close();
		return result;
	}
	
	public List<ResultMapping.ColumnInfo> queryColumnInfo(String tableName) throws SQLException{
		String sql = "select * from "+tableName+" where 1<>1";
		Statement stmt = getConnection().createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		List<ColumnInfo> ci = ResultMapping.getColumnInfo(rs);
		rs.close();
		stmt.close();
		return ci;
	}

	public List<Map<String, Object>> query2(String sql) throws SQLException {
		Statement stmt = getConnection().createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		List<Map<String, Object>> list = ResultMapping.getMapList(rs);
		rs.close();
		stmt.close();
		return list;
	}

	public ResultVo getDatabaseMetaData(String...type) throws SQLException {
		Connection conn = getConnection();
		DatabaseMetaData metaData = conn.getMetaData();
		String catalog = conn.getCatalog();
		ResultSet rs = metaData.getTables(catalog, null, null, type);
		return getResultVo(rs);
	}

	public ResultVo getTableMetaData(String tableName) throws SQLException {
		Connection conn = getConnection();
		DatabaseMetaData metaData = conn.getMetaData();
		String catalog = conn.getCatalog();
		ResultSet rs = metaData.getColumns(catalog, null, tableName, null);
		return getResultVo(rs);
	}
	
	private ResultVo getResultVo(ResultSet rs) throws SQLException{
		ResultVo result = new ResultVo();
		result.setRowsData(ResultMapping.getMapList(rs));
		result.setColNames(ResultMapping.getColumnNames(rs));
		rs.close();
		return result;
	}

	@SuppressWarnings("rawtypes")
	public static class ResultVo{
		private List<String> colNames;
		private Integer rowsNum;
		private List rowsData;
		
		public Integer getRowsNum() {
			return rowsNum;
		}
		public void setRowsNum(Integer rowsNum) {
			this.rowsNum = rowsNum;
		}
		public List getRowsData() {
			return rowsData;
		}
		public void setRowsData(List rowsData) {
			this.rowsData = rowsData;
		}
		public List<String> getColNames() {
			return colNames;
		}
		public void setColNames(List<String> colNames) {
			this.colNames = colNames;
		}
	}
}
